package net.suncaper.mvc.mapper.impl;

import net.suncaper.mvc.mapper.HotelMapper;
import net.suncaper.mvc.model.domain.Hotel;
import net.suncaper.mvc.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Hotel表数据库访问接口实现类
 */
public class HotelMapperImpl implements HotelMapper {
    private static final QueryRunner RUNNER = new QueryRunner();

    /**
     * 添加酒店
     *
     * @param hotel 酒店信息
     * @return 添加结果
     */
    @Override
    public boolean insertHotel(Hotel hotel) {
        String sql = "INSERT INTO hotel(hotel_name, country, city, image_url, longitude, latitude, rating) VALUES (?, ?, ?, ?, ?, ?, ?)";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    hotel.getHotelName(), hotel.getCountry(), hotel.getCity(),
                    hotel.getImageUrl(),
                    hotel.getLongitude(), hotel.getLatitude(), hotel.getRating()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 根据酒店名称获取酒店信息
     *
     * @param hotelName 酒店名称
     * @return 酒店信息
     */
    @Override
    public Hotel getHotelByName(String hotelName) {
        String sql = "SELECT * FROM hotel WHERE hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        Hotel hotel = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, hotelName);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                hotel = new Hotel();
                hotel.setHotelName(rs.getString("hotel_name"));
                hotel.setCountry(rs.getString("country"));
                hotel.setCity(rs.getString("city"));
                hotel.setImageUrl(rs.getString("image_url"));
                hotel.setLongitude(rs.getBigDecimal("longitude"));
                hotel.setLatitude(rs.getBigDecimal("latitude"));
                hotel.setRating(rs.getBigDecimal("rating"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return hotel;
    }

    /**
     * 获取所有酒店信息
     *
     * @return 酒店列表
     */
    @Override
    public List<Hotel> getAllHotels() {
        String sql = "SELECT * FROM hotel";
        Connection connection = JDBCUtils.getConnection();
        List<Hotel> hotels = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Hotel hotel = new Hotel();
                hotel.setHotelName(rs.getString("hotel_name"));
                hotel.setCountry(rs.getString("country"));
                hotel.setCity(rs.getString("city"));
                hotel.setImageUrl(rs.getString("image_url"));
                hotel.setLongitude(rs.getBigDecimal("longitude"));
                hotel.setLatitude(rs.getBigDecimal("latitude"));
                hotel.setRating(rs.getBigDecimal("rating"));
                hotels.add(hotel);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return hotels;
    }

    /**
     * 根据国家和城市筛选酒店
     *
     * @param country 国家
     * @param city    城市
     * @return 酒店列表
     */
    @Override
    public List<Hotel> getHotelsByLocation(String country, String city) {
        String sql = "SELECT * FROM hotel WHERE country = ? AND city = ?";
        Connection connection = JDBCUtils.getConnection();
        List<Hotel> hotels = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, country);
            pstmt.setString(2, city);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Hotel hotel = new Hotel();
                hotel.setHotelName(rs.getString("hotel_name"));
                hotel.setCountry(rs.getString("country"));
                hotel.setCity(rs.getString("city"));
                hotel.setImageUrl(rs.getString("image_url"));
                hotel.setLongitude(rs.getBigDecimal("longitude"));
                hotel.setLatitude(rs.getBigDecimal("latitude"));
                hotel.setRating(rs.getBigDecimal("rating"));
                hotels.add(hotel);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return hotels;
    }

    /**
     * 根据评分筛选酒店
     *
     * @param minRating 最低评分
     * @param maxRating 最高评分
     * @return 酒店列表
     */
    @Override
    public List<Hotel> getHotelsByRating(BigDecimal minRating, BigDecimal maxRating) {
        String sql = "SELECT * FROM hotel WHERE rating BETWEEN ? AND ?";
        Connection connection = JDBCUtils.getConnection();
        List<Hotel> hotels = new ArrayList<>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            pstmt = connection.prepareStatement(sql);
            pstmt.setBigDecimal(1, minRating);
            pstmt.setBigDecimal(2, maxRating);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                Hotel hotel = new Hotel();
                hotel.setHotelName(rs.getString("hotel_name"));
                hotel.setCountry(rs.getString("country"));
                hotel.setCity(rs.getString("city"));
                hotel.setImageUrl(rs.getString("image_url"));
                hotel.setLongitude(rs.getBigDecimal("longitude"));
                hotel.setLatitude(rs.getBigDecimal("latitude"));
                hotel.setRating(rs.getBigDecimal("rating"));
                hotels.add(hotel);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt);
            JDBCUtils.close();
        }
        return hotels;
    }

    /**
     * 更新酒店信息
     *
     * @param hotel 酒店信息
     * @return 更新结果
     */
    @Override
    public boolean updateHotel(Hotel hotel) {
        String sql = "UPDATE hotel SET country = ?, city = ?, image_url = ?, longitude = ?, latitude = ?, rating = ? WHERE hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    hotel.getCountry(), hotel.getCity(),
                    hotel.getImageUrl(), hotel.getLongitude(),
                    hotel.getLatitude(), hotel.getRating(), hotel.getHotelName()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 删除酒店
     *
     * @param hotelName 酒店名称
     * @return 删除结果
     */
    @Override
    public boolean deleteHotel(String hotelName) {
        String sql = "DELETE FROM hotel WHERE hotel_name = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    hotelName
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }
    
    /**
     * 关闭资源
     * 
     * @param rs ResultSet对象
     * @param stmt PreparedStatement对象
     */
    private void closeResources(ResultSet rs, PreparedStatement stmt) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}